<?php
 /**
 * 功能描述：	访客统计
 * @datetime	2020-09-23
 * @version   	v1.0
 * @author   	ZHL
 * @copyrigh  	2020 ZHL 版权所有
 */
class C_ClientModel extends Model {

    /**
     * Notes:判断表是否存在
     * User: ZhuHaili
     * Date: 2023/12/12
     * @return bool
     */
    public function isTable(){
        $db = $this->conn('mysql');
        $dbName = $db->db_name;
        $table = $db->db_fix.countTableName();
        $sql = "select * from information_schema.TABLES where table_schema = '$dbName' and TABLE_NAME = '$table'";
        $isTable = $db->query($sql, 1);
        if ($isTable) {
            return true;
        }
        $sql = "CREATE TABLE `".$table."` (
                  `id` int unsigned NOT NULL AUTO_INCREMENT COMMENT 'id',
                  `ip` varchar(20) NOT NULL COMMENT '访客ip',
                  `new_user` char(1) NOT NULL DEFAULT 'N' COMMENT '当日新访客标识',
                  `page_url` varchar(200) NOT NULL COMMENT '访问页面url',
                  `page_title` varchar(50) DEFAULT NULL COMMENT '访问页面名',
                  `page_rate` tinyint unsigned NOT NULL DEFAULT '0' COMMENT '访问页面百分比',
                  `date_start` datetime DEFAULT NULL COMMENT '创建时间',
                  `date_end` datetime DEFAULT NULL COMMENT '离开时间',
                  `from_url` varchar(200) DEFAULT NULL COMMENT '来源url',
                  `from_title` varchar(20) DEFAULT NULL COMMENT '来源平台（如百度、谷歌）',
                  `from_os` varchar(20) DEFAULT NULL COMMENT '来源系统（如win、ios、Android）',
                  `from_app` varchar(50) DEFAULT NULL COMMENT '来源应用（如谷歌、Edge、Firefox）',
                  `country` varchar(200) DEFAULT NULL COMMENT '国家',
                  `province` varchar(200) DEFAULT NULL COMMENT '省份',
                  `city` varchar(200) DEFAULT NULL COMMENT '城市',
                  `district` varchar(200) DEFAULT NULL COMMENT '地区',
                  PRIMARY KEY (`id`) USING BTREE,
                  KEY `date_start` (`date_start`) USING BTREE
                ) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 ROW_FORMAT=DYNAMIC COMMENT='zhl_访客统计';";
        $request = $db->query($sql);
        if(!$request){
            return false;
        }
        return true;
    }

    /**
     * Notes:新增统计信息
     * User: ZhuHaili
     * Date: 2023/12/12
     * @param $param
     * @return int
     */
    public function addCountInfo($param){
        if(!$this->isTable()){
            return 0;
        }
        $sql['data'] = $param;
        $sql['table'] = countTableName();
        $db = $this->conn('mysql');
        $db->add($sql);
        if($db->rowCount() > 0){
            return $db->returnId();
        }
        return 0;
    }

    /**
     * Notes:修改统计信息
     * User: ZhuHaili
     * Date: 2023/12/12
     * @param $data
     * @param $where
     * @param $table
     * @return bool
     */
    public function updateCountInfo($data, $where, $table){
        if(!$this->isTable()){
            return false;
        }
        $sql['data'] = $data;
        $sql['where'] = $where;
        $sql['table'] = $table;
        $db = $this->conn('mysql');
        $db->update($sql);
        return $db->rowCount() > 0;
    }

    /**
     * Notes:获取时间段统计
     * User: ZhuHaili
     * Date: 2023/12/14
     */
    public function getDayStat($dateStart, $dateEnd){
        if(!$this->isTable()){
            return false;
        }
        $db = $this->conn('mysql');
        $table = $db->db_fix.countTableName();
        $sql = "select sum(pvs) pvs,sum(uvs) uvs,
                sum(case when new_user='Y' then 1 else 0 end) ips from 
                (
                SELECT count(id) pvs,if(new_user = 'Y',count(new_user),0) uvs,ip, new_user
                FROM `$table` where date_start >= '$dateStart' and date_start <= '$dateEnd' 
                group by new_user,ip
                ) a ";
        return $db->query($sql, 1, true);
    }

    /**
     * Notes:获取最近几日走势图
     * User: ZhuHaili
     * Date: 2023/12/14
     */
    public function getRecentTrend($dateStart, $dateEnd){
        $db = $this->conn('mysql');
        $table = $db->db_fix.countTableName();
        $sql = "select sum(pvs) pvs,sum(uvs) uvs,
                sum(case when new_user='Y' then 1 else 0 end) ips,
                date from 
                (
                SELECT count(id) pvs,if(new_user = 'Y',count(new_user),0) uvs,ip, new_user,date(date_start) date
                FROM `$table` where date_start >= '$dateStart' and date_start <= '$dateEnd' 
                group by new_user,ip,date(date_start)
                ) a group by date";
        return $db->query($sql, 1);
    }

    /**
     * Notes:获取最近30天应用比例
     * User: ZhuHaili
     * Date: 2023/12/14
     */
    public function getAppRatio($dateStart, $dateEnd){
        $sql['field'] = 'count(from_app) sum,from_app title';
        $sql['table'] = countTableName();
        $sql['gts']['date_start'] = $dateStart;
        $sql['lts']['date_start'] = $dateEnd;
        $sql['group'] = 'from_app';
        $sql['order'] = 'count(from_app) desc';
        return $this->conn('mysql')->select($sql);
    }

    /**
     * Notes:今日访客详情
     * User: ZhuHaili
     * Date: 2023/12/14
     */
    public function getDayCountInfos($dateStart, $dateEnd){
        $sql['field'] = '*';
        $sql['table'] = countTableName();
        $sql['gts']['date_start'] = $dateStart;
        $sql['lts']['date_start'] = $dateEnd;
        $sql['order'] = 'date_start desc';
        return $this->conn('mysql')->select($sql);
    }

    /**
     * Notes:根据IP获取页面访问排行榜
     * User: ZhuHaili
     * Date: 2023/12/14
     */
    function getPageIpsCounts($dateStr, $dateEnd, $limit = 0){
        $db = $this->conn('mysql');
        $table = $db->db_fix.countTableName();
        $sql = "select count(t.ip) ipcounts, sum(pvs) pvs, t.page_url,t.page_title from (
                    select ip, count(id) pvs, page_url, page_title from $table    
                    where date_start >= '$dateStr' and date_start <= '$dateEnd'   
                    group by page_url,page_title,ip 
                ) t group by t.page_url,t.page_title
                order by ipcounts desc, pvs desc";
        if($limit){
            $sql .= " limit $limit";
        }
        return $db->query($sql, 1);
    }

    /**
     * Notes:页面访问排行榜详情
     * User: ZhuHaili
     * Date: 2023/12/14
     */
    function getPageIpsCountInfo($dateStart, $dateEnd, $pageUrl){
        $sql['field'] = 'count(ip) ipcounts,ip ipstr';
        $sql['table'] = countTableName();
        $sql['where']['page_url'] = $pageUrl;
        $sql['gts']['date_start'] = $dateStart;
        $sql['lts']['date_start'] = $dateEnd;
        $sql['group'] = ' ip';
        $sql['order'] = ' ipcounts desc';
        return $this->conn('mysql')->select($sql);
    }

}